package common.main.newsWangyiSmart;

import java.sql.*;
import java.util.List;

public class NewsWangyiSmartSql {

    static String databaseHost = "192.168.3.76:3306";
    static String databaseName = "ai_intelligence";
    //static String databaseHost = "localhost:3306";
    //static String databaseName = "junkewei";
    static String userName = "root";
    static String password = "123456";
    static String url ="jdbc:mysql://"+databaseHost+"/"
            +databaseName+"?user="+userName+"&password="+password+"&useUnicode=true&characterEncoding=UTF-8&useSSL=false";

    static Connection conn;
    static Statement stmt;

    public static void connectDatabase() throws ClassNotFoundException, IllegalAccessException, InstantiationException, SQLException {
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        if(conn == null || conn.isClosed()){
            conn = DriverManager.getConnection(url);
        }
        if(stmt == null || stmt.isClosed()){
            stmt = conn.createStatement();
        }
    }

    public static String getAllMd5(){
        String sql = "SELECT MD5 FROM `datasource_new` WHERE `Language` = 'cn';";
        try{
            connectDatabase();
            ResultSet rs = stmt.executeQuery(sql);
            StringBuilder sb = new StringBuilder();
            while (rs.next()){
                sb.append(rs.getString("MD5"));
            }
            return sb.toString();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return "";
    }

    public static void saveData(List<NewsWangyiSmartEntity> list){
        String modelSql = "insert into datasource_new " +
                "(TITLE,PUBTIME,SOURCE,URL,MD5,CONTENT,BRIEF,IMG_URL,`Language`,Tag,htmlContent) " +
                "values('<TITLE>','<PUBTIME>','<SOURCE>','<URL>','<MD5>','<CONTENT>','<BRIEF>','<IMG_URL>','cn','<Tag>','<htmlContent>')";
        try {
            connectDatabase();
            for(NewsWangyiSmartEntity entity : list){

                try{
                    entity.setTitle(entity.getTitle().replace("'","\\'").trim());
                    entity.setContent(entity.getContent().replace("'","\\'").trim());
                    if(entity.getBrief() != null){
                        entity.setBrief(entity.getBrief().replace("'","\\'").trim());
                    }else{
                        entity.setBrief("");
                    }
                    if(entity.getImgUrl() != null){
                        entity.setImgUrl(entity.getImgUrl().replace("'","\\'").trim());
                    }else{
                        entity.setImgUrl("");
                    }
                    entity.setLabel(entity.getLabel().replace("'","\\'").trim());
                    entity.setSource(entity.getSource().replace("'","\\'").trim());

                    //日期处理
                    if(entity.getPubTime().contains("/")){
                        String[] dataTime = entity.getPubTime().split(" ");
                        String [] yearMonthDay = dataTime[0].split("/");
                        entity.setPubTime(yearMonthDay[2]+ "-" + yearMonthDay[0] + "-" + yearMonthDay[1] + " " + dataTime[1]);
                    }
                    entity.setHtmlContent(entity.getHtmlContent().replace("'","\\'").trim());

                    stmt.executeUpdate(modelSql.replace("<TITLE>",entity.getTitle())
                            .replace("<PUBTIME>",entity.getPubTime())
                            .replace("<SOURCE>",entity.getSource())
                            .replace("<URL>",entity.getUrl())
                            .replace("<MD5>",entity.getMd5())
                            .replace("<CONTENT>",entity.getContent())
                            .replace("<BRIEF>",entity.getBrief())
                            .replace("<IMG_URL>",entity.getImgUrl())
                            .replace("<Tag>",entity.getLabel())
                            .replace("<htmlContent>",entity.getHtmlContent()));

                } catch (NullPointerException e){
                    System.out.println("空指针异常：" + entity.toString());
                } catch (Exception e) {
                    System.out.println("数据插入异常：" + modelSql.replace("<TITLE>",entity.getTitle())
                            .replace("<PUBTIME>",entity.getPubTime())
                            .replace("<SOURCE>",entity.getSource())
                            .replace("<URL>",entity.getUrl())
                            .replace("<MD5>",entity.getMd5())
                            .replace("<CONTENT>",entity.getContent())
                            .replace("<BRIEF>",entity.getBrief())
                            .replace("<IMG_URL>",entity.getImgUrl())
                            .replace("<Tag>",entity.getLabel())
                            .replace("<htmlContent>",entity.getHtmlContent()));
                    e.printStackTrace();
                }
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
}
